package cn.edu.klmy.dao.impl;

import cn.edu.klmy.dao.AbstractJdbcTemplate;
import cn.edu.klmy.dao.IBlackListDao;
import cn.edu.klmy.models.blacklist.BlackList;
import cn.edu.klmy.models.student.Student;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 顾伟
 *         创建日期：13-5-1
 */
@Repository
public class BlackListDao extends AbstractJdbcTemplate implements IBlackListDao {

    public int addBlackList(int teacherId) {
        String sql = "SELECT BLACK_LIST_ID FROM TEACHER WHERE ID=" + teacherId;
        int blackListId = this.jdbcTemplate.queryForInt(sql);
        if (blackListId == 0) {
            SimpleJdbcInsert insert = new SimpleJdbcInsert(this.jdbcTemplate);
            insert.withTableName("BLACK_LIST");
            insert.setGeneratedKeyName("BLACK_LIST_ID");
            insert.compile();

            Map<String, Object> args = new HashMap<String, Object>();
            args.put("BLACK_LIST_CODE", "");
            Number id = insert.executeAndReturnKey(args);
            blackListId = id.intValue();

            String updateSql="UPDATE TEACHER SET BLACK_LIST_ID="+blackListId
                    +" WHERE ID="+teacherId;
            this.jdbcTemplate.update(updateSql);
        }
        return blackListId;
    }

    public List<Student> getBlackListStudents(int blackListId) {
        String sql = "SELECT * FROM STUDENT WHERE ID IN (SELECT STUDENT_ID FROM STUDENT_BLACK_LIST WHERE BLACK_LIST_ID=" + blackListId + ")";
        return this.jdbcTemplate.query(sql, new StudentRowMapper());
    }

    public List<Student> getNunBlackListStudents(int blackListId) {
        String sql = "SELECT * FROM STUDENT WHERE ID NOT IN (SELECT STUDENT_ID FROM STUDENT_BLACK_LIST WHERE BLACK_LIST_ID=" + blackListId + ")";
        return this.jdbcTemplate.query(sql, new StudentRowMapper());
    }

    public void addStudentToBlackList(int blackListId, int studentId) {
        SimpleJdbcInsert insert = new SimpleJdbcInsert(this.jdbcTemplate);
        insert.withTableName("STUDENT_BLACK_LIST");
        insert.compile();

        Map<String, Object> args = new HashMap<String, Object>();
        args.put("BLACK_LIST_ID", blackListId);
        args.put("STUDENT_ID", studentId);

        insert.execute(args);
    }

    public void deleteStudentFromBlackList(int blackListId, int studentId) {
        String sql = "DELETE FROM STUDENT_BLACK_LIST WHERE BLACK_LIST_ID=" + blackListId + " AND STUDENT_ID=" + studentId;
        this.jdbcTemplate.update(sql);
    }

    private class StudentRowMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            Student student = new Student();
            student.setId(resultSet.getInt("ID"));
            student.setBirthday(resultSet.getString("STUDENT_BIRTH"));
            student.setName(resultSet.getString("STUDENT_NAME"));
            student.setNation(resultSet.getInt("STUDENT_RACE"));
            student.setSex(resultSet.getInt("STUDENT_SEX"));
            student.setIdentityCardNumber(resultSet.getString("STUDENT_ID_NO"));
            student.setStudentNumber(resultSet.getString("STUDENT_NO"));
            student.setExecutiveClass(resultSet.getInt("ADMIN_CLASS_ID"));
            student.setTeachingClass(resultSet.getInt("STUDY_CLASS_ID"));
            return student;
        }
    }

    private class BlackListRowMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            BlackList blackList = new BlackList();
            blackList.setBlackListId(resultSet.getInt("BLACK_LIST_ID"));
            blackList.setBlackListName(resultSet.getString("BLACK_LIST_CODE"));
            return blackList;
        }
    }
}
